USE [Weval test]
GO
/****** Object:  StoredProcedure [dbo].[QuestionDetails]    Script Date: 03/03/2014 17:43:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[QuestionDetails] @CRN int, @semester int, @year int, @questionID int
AS
--30663 33037
--DECLARE @CRN int, @semester int, @year int, @questionID int
--SET @CRN = 33037
--SET @semester = 3
--SET @year = 2013
--SET @questionID = 3979590

DECLARE @instructorID int
DECLARE @college nvarchar(20)
DECLARE @subjectCode nvarchar(20)
DECLARE @class varchar(5)
DECLARE @questionText nvarchar(400)

SET @instructorID = --Get instructor assigned to class/CRN in question
(
	SELECT DISTINCT TOP 1 ST.instructorID
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester= @semester
);
SET @subjectCode = --Ex: 'CS'
(
	SELECT TOP 1 subjectcode FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);
SET @class = --Ex: '1010'
(
	SELECT TOP 1 CS.courseNumber FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);

SET @questionText = 
(
	SELECT TOP 1 LOWER(CAST(Q.text AS NVARCHAR(255))) FROM Questions Q where Q.ID = @questionID
);

WITH cteMain AS
(
	SELECT
		Q.ID as questionID,
		CS.bannerCRN,
		CS.year,
		CS.semester,
		ST.instructorID,
		CS.subjectcode,
		CS.coursenumber,
		LOWER(CAST(Q.text AS nvarchar(255))) as text,
		CASE
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 1.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 1.0
		END SA,
		CASE
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 1.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 1.0
		END A,
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 1.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 1.0
		END N,
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0
		END D,
		CASE
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 1.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 1.0
		END SD,
		CASE
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'N/A' THEN 1.0
		END NA,
		Q.ID
		
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
			AND (CS.year > (@year-6)) --5 years prior to this class
			AND CS.subjectCode = @subjectCode
		INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
			--AND QR.ID = @questionID
		INNER JOIN Questions Q ON Q.ID = QR.questionID
			AND LOWER(CAST(Q.text AS NVARCHAR(255))) =@questionText
		INNER JOIN Tests T ON T.ID = Q.testID 
			AND T.typeID = 5 --Course Eval
		INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
		INNER JOIN Distractors D ON D.ID = DR.distractorID
		INNER JOIN Users UI ON UI.Id = ST.instructorID 
),

cteDptSemesterAverage AS
(
	SELECT text,
		SUM(SA)as 'DptSemesterAverageSA', 
		SUM(A)as 'DptSemesterAverageA', 
		SUM(N)as 'DptSemesterAverageN', 
		SUM(D)as 'DptSemesterAverageD', 
		SUM(SD)as 'DptSemesterAverageSD',
		SUM(NA)as 'DptSemesterAverageNA',
		((isNull(SUM(SA),0)+isNull(SUM(A),0)+isNull(SUM(N),0)+isNull(SUM(D),0)+isNull(SUM(SD),0)+isNull(SUM(NA),0))) as 'DptSemesterAverageTotal'
	FROM cteMain
	WHERE year = @year AND semester = @semester
	GROUP BY text
),
cteDptFiveYearCourseAverage AS
(
	SELECT text, subjectcode, 
		SUM(SA)as 'DptFiveYearCourseAverageSA', 
		SUM(A)as 'DptFiveYearCourseAverageA', 
		SUM(N)as 'DptFiveYearCourseAverageN', 
		SUM(D)as 'DptFiveYearCourseAverageD', 
		SUM(SD)as 'DptFiveYearCourseAverageSD',
		SUM(NA)as 'DptFiveYearCourseAverageNA',
		((isNull(SUM(SA),0)+isNull(SUM(A),0)+isNull(SUM(N),0)+isNull(SUM(D),0)+isNull(SUM(SD),0)+isNull(SUM(NA),0))) as 'DptFiveYearCourseAverageTotal'
	FROM cteMain
	WHERE courseNumber = @class --these parameters can be ajusted to produce the 5 necessary cases		
	GROUP BY text, subjectcode
),
ctePersonalSemesterAverage AS
(
	SELECT  text, year, semester, instructorID, subjectcode, 
		SUM(SA)as 'PersonalSemesterAverageSA', 
		SUM(A)as 'PersonalSemesterAverageA', 
		SUM(N)as 'PersonalSemesterAverageN', 
		SUM(D)as 'PersonalSemesterAverageD', 
		SUM(SD)as 'PersonalSemesterAverageSD',
		SUM(NA)as 'PersonalSemesterAverageNA',
		((isNull(SUM(SA),0)+isNull(SUM(A),0)+isNull(SUM(N),0)+isNull(SUM(D),0)+isNull(SUM(SD),0)+isNull(SUM(NA),0))) as 'PersonalSemesterAverageTotal'
	FROM cteMain
	WHERE instructorID = @instructorId --these parameters can be ajusted to produce the 5 necessary cases
	AND year = @year AND semester = @semester
	GROUP BY text, year, semester, instructorID, subjectcode
),
ctePersonalFiveYearAverage AS
(
	SELECT  text,instructorID, subjectcode, 
		SUM(SA)as 'PersonalFiveYearAverageSA', 
		SUM(A)as 'PersonalFiveYearAverageA', 
		SUM(N)as 'PersonalFiveYearAverageN', 
		SUM(D)as 'PersonalFiveYearAverageD', 
		SUM(SD)as 'PersonalFiveYearAverageSD',
		SUM(NA)as 'PersonalFiveYearAverageNA',
		((isNull(SUM(SA),0)+isNull(SUM(A),0)+isNull(SUM(N),0)+isNull(SUM(D),0)+isNull(SUM(SD),0)+isNull(SUM(NA),0))) as 'PersonalFiveYearAverageTotal'
	FROM cteMain
	WHERE instructorID = @instructorId --these parameters can be ajusted to produce the 5 necessary cases
		AND courseNumber = @class
	GROUP BY text, instructorID, subjectcode
)

SELECT * FROM
ctePersonalFiveYearAverage AS PFYA,ctePersonalSemesterAverage AS PSA ,cteDptFiveYearCourseAverage AS DFYCA, cteDptSemesterAverage AS DSA
WHERE PFYA.text = PSA.text
	AND PSA.text = DFYCA.text
	AND DSA.text = DFYCA.text
	